package io.itit.smartjdbc.dao;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.mysql.jdbc.Statement;

import io.itit.smartjdbc.Config;
import io.itit.smartjdbc.ResultSetHandler;
import io.itit.smartjdbc.SqlInterceptor;
import io.itit.smartjdbc.connection.ConnectionManager;
import io.itit.smartjdbc.stat.DBStat;
import io.itit.smartjdbc.util.DumpUtil;
import io.itit.smartjdbc.util.JdbcUtil;

/**
 * 
 * @author skydu
 *
 */
public abstract class BaseDAO{
	//
	private static final Logger logger=LoggerFactory.getLogger(BaseDAO.class);
	//
	protected String datasourceIndex;
	//
	public static long SLOW_SQL_MIN_USE_TIME=1000;//1s
	//
	public BaseDAO() {
	}
	//
	protected void beforeExcute(String sql,Object ... parameters) {
		for (SqlInterceptor interceptor : Config.getSqlInterceptors()) {
			interceptor.beforeExcute(sql,parameters);
		}
	}
	//
	protected void afterExcute(String sql,Object ... parameters) {
		for (SqlInterceptor interceptor : Config.getSqlInterceptors()) {
			interceptor.afterExcute(sql,parameters);
		}
	}
	//
	protected int executeUpdate(Connection conn,PreparedStatement ps,boolean returnAutoGeneratedKeys)
	throws SQLException{
		ResultSet rs=null;
		try {
			int rowCount = ps.executeUpdate();
			if(returnAutoGeneratedKeys){//
				int autoIncrease = -1;
				rs = ps.getGeneratedKeys();
				if (rs.next()) {
					autoIncrease = rs.getInt(1);
				}
				return autoIncrease;
			}
			return rowCount;
		} catch (SQLException e) {
			throw e;
		}finally {
			JdbcUtil.close(ps,rs);
		}
	}

	//
	protected int[] executeBatch(Connection conn,PreparedStatement ps)
	throws SQLException{
				ResultSet rs=null;
				try {
					return ps.executeBatch();
				} catch (SQLException e) {
					throw e;
				}finally {
					JdbcUtil.close(ps,rs);
				}
			}
	//
	protected int executeWithGenKey(String sql,Object ... parameters){
		return executeUpdate0(sql, true, parameters);
	}
	//
	protected int executeUpdate0(String sql,boolean returnAutoGeneratedKeys,Object ... parameters){
		int result=-1;
		long startTime=System.currentTimeMillis();
		long useTime=0;
		boolean isException=false;
		try {
			beforeExcute(sql, parameters);
			Connection conn=ConnectionManager.getConnecton(datasourceIndex);
			PreparedStatement ps = conn.prepareStatement(sql.toString(),
					returnAutoGeneratedKeys ? Statement.RETURN_GENERATED_KEYS:Statement.NO_GENERATED_KEYS);
			int index = 1;
			if(parameters!=null){
				for (Object parameter : parameters) {
					ps.setObject(index++, parameter);
				}
			}
			result=executeUpdate(conn, ps,returnAutoGeneratedKeys);
			useTime=System.currentTimeMillis()-startTime;
			return result;
		} catch (Throwable e) {
			isException=true;
			throw new RuntimeException(e.getMessage(),e);
		}finally {
			if(logger.isDebugEnabled()){
				logger.debug("executeUpdate \nisException:{} \nuseTime:{}ms \nresult:{} \nsql:{} \nparameters:{}\n{}",
						isException,
						useTime,
						result,
						sql,
						parameters==null?0:parameters.length,
						dumpParameters(parameters));
			}
			afterExcute(sql, parameters);
			DBStat.stat(sql,useTime,isException);//stat
			if(!isException&&useTime>=SLOW_SQL_MIN_USE_TIME){
				logger.warn("executeUpdate  \nuseTime:{}ms \nresult:{} \nsql:{} \nparameters:{}\n{}",
						useTime,
						result,
						sql,
						parameters==null?0:parameters.length,
						dumpParameters(parameters));
			}
		}
	}
	//
	protected long executeLargeUpdate(Connection conn,PreparedStatement ps,boolean autoGeneratedKeys)
	throws SQLException{
		ResultSet rs=null;
		try {
			long rowCount = ps.executeLargeUpdate();
			if(autoGeneratedKeys){//
				long autoIncrease = -1;
				rs = ps.getGeneratedKeys();
				if (rs.next()) {
					autoIncrease = rs.getLong(1);
				}
				return autoIncrease;
			}
			return rowCount;
		} catch (SQLException e) {
			throw e;
		}finally {
			JdbcUtil.close(ps,rs);
		}
	}
	//
	protected long executeLargeUpdate(String sql,boolean returnAutoGeneratedKeys,Object ... parameters){
		try {
			Connection conn=ConnectionManager.getConnecton(datasourceIndex);
			PreparedStatement ps = conn.prepareStatement(sql.toString(),
					returnAutoGeneratedKeys ? Statement.RETURN_GENERATED_KEYS:Statement.NO_GENERATED_KEYS);
			int index = 1;
			for (Object parameter : parameters) {
				ps.setObject(index++, parameter);
			}
			beforeExcute(sql, parameters);
			return executeLargeUpdate(conn, ps,returnAutoGeneratedKeys);
		} catch (Throwable e) {
			logger.error(e.getMessage(), e);
			throw new RuntimeException(e.getMessage(),e);
		}finally {
			afterExcute(sql, parameters);
		}
	}
	//
	protected int[] executeBatch(String sql,List<Object[]> parameters){
		int[] result=null;
		long startTime=System.currentTimeMillis();
		long useTime=0;
		boolean isException=false;
		try {
			Connection conn=ConnectionManager.getConnecton(datasourceIndex);
			PreparedStatement ps = conn.prepareStatement(sql.toString());
			if(parameters!=null){
				for (Object[] parameter : parameters) {
					int index = 1;
					for (Object obj : parameter) {
						ps.setObject(index++, obj);
					}
					ps.addBatch();
				}
			}
			beforeExcute(sql, parameters);
			result=executeBatch(conn,ps);
			useTime=System.currentTimeMillis()-startTime;
			return result;
		} catch (Throwable e) {
			isException=true;
			throw new RuntimeException(e.getMessage(),e);
		}finally {
			if(logger.isDebugEnabled()){
				logger.debug("executeBatch \nisException:{} \nuseTime:{}ms \nresult:{} \nsql:{} \nparameters:{}\n{}",
						isException,
						useTime,
						result,
						sql,
						parameters==null?0:parameters.size(),
						DumpUtil.dump(parameters));
			}
			afterExcute(sql, parameters);
			DBStat.stat(sql,useTime,isException);//stat
			if(!isException&&useTime>=SLOW_SQL_MIN_USE_TIME){
				logger.warn("executeBatch  \nuseTime:{}ms \nresult:{} \nsql:{} \nparameters:{}\n{}",
						useTime,
						result,
						sql,
						parameters==null?0:parameters.size(),
						DumpUtil.dump(parameters));
			}
		}
	}
	//
	public long largeInsert(String sql,boolean returnAutoGeneratedKeys,Object ... parameters){
		return executeLargeUpdate(sql, returnAutoGeneratedKeys, parameters);
	}
	//
	public int insert(String sql,boolean returnAutoGeneratedKeys,Object ... parameters){
		return executeUpdate0(sql, returnAutoGeneratedKeys, parameters);
	}
	//
	public int[] insertBatch(String sql,List<Object[]> parameters){
		return executeBatch(sql, parameters);
	}
	//
	protected int executeUpdate(String sql,Object ... parameters){
		return executeUpdate0(sql,false,parameters);
	}
	//
	protected <T> T queryForObject(String sql,ResultSetHandler<T> handler,Object ... parameters){
		T bean=null;
		boolean isException=false;
		long startTime=System.currentTimeMillis();
		long useTime=0;
		try {
			Connection conn=ConnectionManager.getConnecton(datasourceIndex);
			PreparedStatement ps = conn.prepareStatement(sql.toString());
			int index = 1;
			for (Object parameter : parameters) {
				ps.setObject(index++, parameter);
			}
			beforeExcute(sql, parameters);
			bean=query(conn, ps, handler);
			useTime=System.currentTimeMillis()-startTime;
			return bean;
		} catch (Throwable e) {
			isException=true;
			logger.error(e.getMessage(), e);
			throw new RuntimeException(e.getMessage(),e);
		}finally {
			if(logger.isDebugEnabled()){
				logger.debug("query \nisException:{} \nuseTime:{}ms \nsql:{} \nparameters:{} \nresult:{}",
						isException,
						useTime,
						sql,
						dumpParameters(parameters),
						DumpUtil.dump(bean));
			}
			afterExcute(sql, parameters);
			DBStat.stat(sql,useTime,isException);//stat
			if(!isException&&useTime>=SLOW_SQL_MIN_USE_TIME){
				logger.warn("query \nisException:{} \nuseTime:{}ms \nsql:{} \nparameters:{} \nresult:{}",
						isException,
						useTime,
						sql,
						dumpParameters(parameters),
						DumpUtil.dump(bean));
			}
		}
	}
	//
	protected <T> List<T> queryForList(String sql,ResultSetHandler<T> handler,Object ... parameters){
		List<T> list=null;
		boolean isException=false;
		long startTime=System.currentTimeMillis();
		long useTime=0;
		try {
			Connection conn=ConnectionManager.getConnecton(datasourceIndex);
			PreparedStatement ps = conn.prepareStatement(sql.toString());
			int index = 1;
			for (Object parameter : parameters) {
				ps.setObject(index++, parameter);
			}
			beforeExcute(sql, parameters);
			list=queryList(conn, ps, handler);
			useTime=System.currentTimeMillis()-startTime;
			return list;
		} catch (Throwable e) {
			isException=true;
			throw new RuntimeException(e.getMessage(),e);
		}finally {
			if(logger.isDebugEnabled()){
				logger.debug("queryList \nisException:{} \nuseTime:{}ms \nsql:{}\nparameters:{}\nresult:{}",
						isException,
						useTime,
						sql,
						dumpParameters(parameters),
						list==null?0:list.size()
						);
			}
			afterExcute(sql, parameters);
			DBStat.stat(sql,useTime,isException);//stat
			if(!isException&&useTime>=SLOW_SQL_MIN_USE_TIME){
				logger.warn("queryList \nisException:{} \nuseTime:{}ms \nsql:{}\nparameters:{}\nresult:{}",
						isException,
						useTime,
						sql,
						dumpParameters(parameters),
						list==null?0:list.size()
						);
			}
		}
	}
	
	protected String dumpParameters(Object[] parameters) {
		return DumpUtil.dump(parameters);
	}
	//
	protected <T> T query(Connection conn,PreparedStatement ps,ResultSetHandler<T> handler) 
	throws Exception{
		ResultSet rs=null;
		try {
			rs = ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					return handler.handleRow(rs); 
				}
			}
			return null;
		} finally {
			JdbcUtil.close(ps,rs);
		}
	}
	//
	protected <T> List<T> queryList(Connection conn,PreparedStatement ps,ResultSetHandler<T> handler) 
	throws Exception{
		ResultSet rs=null;
		try {
			rs = ps.executeQuery();
			List<T> list=new ArrayList<>();
			if(rs!=null){
				while(rs.next()){
					list.add(handler.handleRow(rs)); 
				}
			}
			return list;
		} finally {
			JdbcUtil.close(ps,rs);
		}
	}
	
	//
	protected Boolean queryForBoolean(String sql,Object ...parameters){
		return  queryForObject(sql,rs->rs.getBoolean(1), parameters);
	}
	//
	protected String queryForString(String sql,Object ...parameters){
		return  queryForObject(sql,rs->rs.getString(1), parameters);
	}
	//
	protected Double queryForDouble(String sql,Object ...parameters){
		return  queryForObject(sql,rs->rs.getDouble(1), parameters);
	}
	//
	protected Float queryForFloat(String sql,Object ...parameters){
		return  queryForObject(sql,rs->rs.getFloat(1), parameters);
	}
	//
	protected Integer queryForInteger(String sql,Object ...parameters){
		return  queryForObject(sql,rs->rs.getInt(1), parameters);
	}
	//
	protected Long queryForLong(String sql,Object ...parameters){
		return  queryForObject(sql,rs->rs.getLong(1), parameters);
	}
	//
	protected Short queryForShort(String sql,Object ...parameters){
		return  queryForObject(sql,rs->rs.getShort(1), parameters);
	}
	//
	protected BigDecimal queryForBigDecimal(String sql,Object ...parameters){
		return  queryForObject(sql,rs->rs.getBigDecimal(1), parameters);
	}
	//
	protected Byte queryForByte(String sql,Object ...parameters){
		return  queryForObject(sql,rs->rs.getByte(1), parameters);
	}
	//
	protected Date queryForDate(String sql,Object ...parameters){
		return  queryForObject(sql,rs->rs.getTimestamp(1), parameters);
	}
	//
	protected List<Boolean> queryForBooleans(String sql,Object ...parameters){
		return  queryForList(sql,rs->rs.getBoolean(1), parameters);
	}
	//
	protected List<String> queryForStrings(String sql,Object ...parameters){
		return  queryForList(sql,rs->rs.getString(1), parameters);
	}
	//
	protected List<Double> queryForDoubles(String sql,Object ...parameters){
		return  queryForList(sql,rs->rs.getDouble(1), parameters);
	}
	//
	protected List<Float> queryForFloats(String sql,Object ...parameters){
		return  queryForList(sql,rs->rs.getFloat(1), parameters);
	}
	//
	protected List<Integer> queryForIntegers(String sql,Object ...parameters){
		return  queryForList(sql,rs->rs.getInt(1), parameters);
	}
	//
	protected List<Long> queryForLongs(String sql,Object ...parameters){
		return  queryForList(sql,rs->rs.getLong(1), parameters);
	}
	//
	protected List<Short> queryForShorts(String sql,Object ...parameters){
		return  queryForList(sql,rs->rs.getShort(1), parameters);	
	}
	//
	protected List<BigDecimal> queryForBigDecimals(String sql,Object ...parameters){
		return  queryForList(sql,rs->rs.getBigDecimal(1), parameters);
	}
	//
	protected List<Byte> queryForBytes(String sql,Object ...parameters){
		return  queryForList(sql,rs->rs.getByte(1), parameters);
	}
	//
	protected List<Date> queryForDates(String sql,Object ...parameters){
		return  queryForList(sql,rs->rs.getTimestamp(1), parameters);
	}
	//
	public String getDatasourceIndex() {
		return datasourceIndex;
	}
	//
	public void setDatasourceIndex(String datasourceIndex) {
		this.datasourceIndex = datasourceIndex;
	}
	//
}
